Release 10.1A: OpenEdge Data Management:
DataServer for ODBC

Data types

ODBC data-source data types differ from OpenEdge data types. However, each data-source data type supported by the DataServer has at least one OpenEdge equivalent.

The DataServer translates ODBC data-source data types into OpenEdge equivalents and places the mapping into the schema holder. You can access this mapping information using the Data Dictionary. Note, however, that the Data Dictionary lists the ODBC SQL equivalents for data-source data types rather than the actual data-source data types. For example, the Data Dictionary lists the Sybase datetime and smalldatetime data types as timestamp, which is the equivalent ODBC SQL data type and not the Sybase timestamp data type. There may also be some restrictions in data-source compatibility with OpenEdge. For example, the OpenEdge database cannot support a numeric or decimal field defined in Sybase with greater than 28 digits.

You can also modify these definitions using the Data Dictionary. For example, the DataServer maps the Sybase tinyint data type to the OpenEdge equivalent, INTEGER. Suppose, however, that your application uses the tinyint field in such a way that the LOGICAL data type is a more suitable equivalent. In this case, you would change the data type from INTEGER to LOGICAL in the schema holder. If you do change a data-type mapping, be sure to select a data type that accommodates the data in the column, otherwise conversion errors might occur at run time. Also, remember to specify a display format that is appropriate for the new data type. See the "Modifying a schema holder" section for an explanation of how to use the Data Dictionary to change OpenEdge data types in the schema holder.

The tables in the following sections list, for each ODBC data source, the data types supported by the DataServer, the ODBC SQL equivalents, and the default OpenEdge equivalents. The notes that follow some tables provide additional information.

DB2

Table 2–3 lists the DB2 data types supported by the DataServer, their ODBC SQL equivalents, and their default OpenEdge equivalents. The data types in parentheses are alternative data types that you can specify in the schema holder for your DB2 data source. You cannot change the list of default data types in a schema holder for a DB2 data source.

Table 2–3: DB2 Data-type equivalencies (

DB2 Data type	SQL-ODBC data type	OpenEdge default
integer	SQL_TYPE_INTEGER	INTEGER (DECIMAL or LOGICAL)
smallint	SQL_TYPE_SMALLINT	INTEGER (DECIMAL or LOGICAL)
decimal1	SQL_TYPE_DECIMAL	DECIMAL (INTEGER)
real/float2	SQL_TYPE_REAL	DECIMAL (INTEGER)
double precision/float2	SQL_TYPE_FLOAT	DECIMAL (INTEGER)
date	SQL_TYPE_TIMESTAMP	CHARACTER (DATE)
time	SQL_TYPE_TIMESTAMP	CHARACTER3
timestamp	SQL_TYPE_TIMESTAMP	DATETIME or DATETIME-TZ (CHARACTER)5 (DATE3,4)
char(n)	SQL_TYPE_CHARACTER	CHARACTER
varchar(n)	SQL_TYPE_VARCHAR	CHARACTER
long varchar(n)	SQL_TYPE_LONGVARCHAR	CHARACTER
graphic	SQL_TYPE_BINARY	CHARACTER
vargraphic(n)	SQL_TYPE_VARBINARY	CHARACTER
long vargraphic	SQL_TYPE_LONGBINARY	CHARACTER
The DataServer truncates values in DB2 decimal or numeric columns defined with a scale greater than 10. In the case of float columns, it reliably translates up to 10 places only. Do not use the float or real data types in joins, in primary indexes, or with the equality operator. When you change the default mapping of the DB2 timestamp or time data types to the OpenEdge DATE data type, OpenEdge truncates the time portion of the date. The DB2 timestamp data type contains both date and time information. The DataServer maps this to the OpenEdge CHARACTER data type; however, you can change the CHARACTER data type to DATE in the schema holder. If you do, remember to change the format to match the new data type. For example, if you change the data type to DATE, specify a date format, such as 99/99/99. OpenEdge supports an emulation process to determine time zone details. For more information, see the XXX section. (bhamel - specifics of this statement and the specific details about this emulation process still need to be determined and located later in this chapter.)

Informix

Table 2–4 lists the Informix data types supported by the DataServer, their ODBC SQL equivalents, and their default OpenEdge equivalents.

Table 2–4: Informix data-type equivalencies 

Informix data type	SQL-ODBC data type	OpenEdge default
Byte	SQL_TYPE_LONGVARBINARY	CHARACTER
Char	SQL_TYPE_CHAR	CHARACTER
Date	SQL_TYPE_DATE	DATE
Decimal	SQL_TYPE_DECIMAL	DECIMAL
Float	SQL_TYPE_DOUBLE	DECIMAL
Integer	SQL_TYPE_INTEGER	INTEGER
Money	SQL_TYPE_DECIMAL	DECIMAL
Serial	SQL_TYPE_INTEGER	INTEGER
Smallfloat	SQL_TYPE_REAL	DECIMAL
Smallint	SQL_TYPE_SMALLINT	INTEGER
Text	SQL_TYPE_LONGVARCHAR	CHARACTER
Varchar	SQL_TYPE_VARCHAR	CHARACTER

Sybase

Table 2–5 lists the Sybase data types, their ODBC SQL equivalents, and their default OpenEdge equivalents. The data types in parentheses are alternative data types that you can specify in the schema holder for your Sybase data source.

Table 2–5: Sybase data-type equivalencies (

Sybase data type	SQL-ODBC data type	OpenEdge default
integer	SQL_TYPE_INTEGER	INTEGER (DECIMAL)
smallint	SQL_TYPE_SMALLINT	INTEGER (DECIMAL or LOGICAL)
tinyint	SQL_TYPE_TINYINT	INTEGER (DECIMAL or LOGICAL)
decimal	SQL_TYPE_DECIMAL	DECIMAL (INTEGER)
numeric1	SQL_TYPE_DECIMAL	DECIMAL (INTEGER)
float2	SQL_TYPE_FLOAT	DECIMAL (INTEGER)
double precision	SQL_TYPE_DOUBLE	DECIMAL (INTEGER)
real	SQL_TYPE_REAL	DECIMAL (INTEGER)
char3	SQL_TYPE_CHAR	CHARACTER
varchar3	SQL_TYPE_VARCHAR	CHARACTER
nchar3,4	SQL_TYPE_CHAR	CHARACTER
nvarchar4	SQL_TYPE_VARCHAR	CHARACTER
text, ntext	SQL_TYPE_LONGVARCHAR	CHARACTER5
money	SQL_TYPE_DECIMAL	DECIMAL (INTEGER)
smallmoney	SQL_TYPE_DECIMAL	DECIMAL (INTEGER)
datetime	SQL_TYPE_TIMESTAMP	DATETIME or DATETIME-TZ10 (CHARACTER6) (DATE7)
smalldatetime	SQL_TYPE_TIMESTAMP	DATETIME or DATETIME-TZ10 (CHARACTER6) (DATE7)
binary3	SQL_TYPE_BINARY	CHARACTER
varbinary	SQL_TYPE_VARBINARY	CHARACTER
image	SQL_TYPE_LONGVARBINARY	CHARACTER5
bit	SQL_TYPE_BIT	LOGICAL
timestamp8	SQL_TYPE_VARBINARY	Unsupported
identity	NA	You can only display these values.9
The DataServer truncates values in Sybase decimal or numeric columns defined with a scale greater than 10. In the case of float columns, it reliably translates up to 10 places only. Do not use the float or real data types in joins, in primary keys, or with the equality operator. When you define a binary or char column to allow nulls, Sybase stores the data type definitions as varbinary and varchar respectively. The equivalent SQL data types that the Data Dictionary displays for these are SQL VARBINARY, SQL VARCHAR, and SQL VARCHAR. This does not affect how the DataServer maps the Sybase data types to OpenEdge data types. You can access nchar and nvarchar data types as Sybase objects and bypass their conversion to CHARACTER by running a Sybase stored procedure or using the send–sql–statement option supported by the DataServer. Although the Sybase text and image fields can hold up to 2MB, OpenEdge retrieves only up to 32K. If you are using an OpenEdge Format phrase, there might be additional limits on the size of text and image fields. See the Format Phrase entry in the OpenEdge Development: Progress 4GL Reference . You can use the DataServer (-Dsrv MAX_LENGTH) startup parameter to limit the amount of returned text data. By default, the initial value of a Sybase datetime or smalldatetime column is unknown ("?"). The default initial values for binary and varbinary are also unknown (“?”). The Sybase datetime and smalldatetime data types contain both date and time information. The DataServer maps these to the OpenEdge CHARACTER data type; however, you can change the CHARACTER data type to INTEGER or DATE in the schema holder. If you do this, remember to change the format to match the new data type. For example, if you change the data type to DATE, specify a date format, such as 99/99/99. When you change the default mapping of Sybase datetime or smalldatetime data types to the OpenEdge DATE data type, OpenEdge truncates the time portion of the date. The DataServer considers a timestamp data type to be a hidden value. It is not visible to the user, but you can still access a Sybase table that contains a timestamp column. You can display values in identity columns, but you cannot insert or update them. OpenEdge supports an emulation (or evaluation??) process to determine time zone details. For more information, see the "ODBC Implementation for the DATETIME and DATETIME-TZ data types" section. (bhamel - specifics of this statement and the specific details about this emulation process still need to be determined and located later in this chapter.)